package cn.kgc.dao.impl;

import cn.kgc.dao.BaseDao;
import cn.kgc.dao.StuAndProDao;
import cn.kgc.entity.StuAndPro;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

public class StuAndDaoImpl extends BaseDao implements StuAndProDao {
    @Override
    public List<StuAndPro> search(String stunum,String name,String specialty,int currentPage,int currentSize) {
        String names = "%"+name+"%";
        String sql = null;
        if((stunum!=null&&!"".equals(stunum))&&(name!=null&&!"".equals(name))&&(specialty!=null&&!"".equals(specialty))){
            sql = "SELECT stu.stunum,stu.name,stu.specialty,pro.course,pro.score FROM school_student stu INNER JOIN school_project pro on stu.stunum = pro.stunum WHERE stu.stunum = ? and stu.name like '"+names+"' and stu.specialty = ? limit ?,?";
            return this.query(StuAndPro.class,sql,stunum,specialty,(currentPage-1)*currentSize,currentSize);
        }else if((stunum!=null&&!"".equals(stunum))&&(name==null||"".equals(name))&&(specialty!=null&&!"".equals(specialty))){
            sql = "SELECT stu.stunum,stu.name,stu.specialty,pro.course,pro.score FROM school_student stu INNER JOIN school_project pro on stu.stunum = pro.stunum WHERE stu.stunum = ? and stu.specialty = ? limit ?,?";
            return this.query(StuAndPro.class,sql,stunum,specialty,(currentPage-1)*currentSize,currentSize);
        }else if((stunum==null||"".equals(stunum))&&(name!=null&&!"".equals(name))&&(specialty!=null&&!"".equals(specialty))){
            sql = "SELECT stu.stunum,stu.name,stu.specialty,pro.course,pro.score FROM school_student stu INNER JOIN school_project pro on stu.stunum = pro.stunum WHERE stu.name like '"+names+"' and stu.specialty = ? limit ?,?";
            return this.query(StuAndPro.class,sql,specialty,(currentPage-1)*currentSize,currentSize);
        }else if((stunum==null||"".equals(stunum))&&(name==null||"".equals(name))&&(specialty==null||"".equals(specialty))){
            sql = "SELECT stu.stunum,stu.name,stu.specialty,pro.course,pro.score FROM school_student stu INNER JOIN school_project pro on stu.stunum = pro.stunum limit ?,?";
            return this.query(StuAndPro.class,sql,(currentPage-1)*currentSize,currentSize);
        }else if((stunum!=null&&!"".equals(stunum))&&(name==null||"".equals(name))&&(specialty==null||"".equals(specialty))){
            sql = "SELECT stu.stunum,stu.name,stu.specialty,pro.course,pro.score FROM school_student stu INNER JOIN school_project pro on stu.stunum = pro.stunum WHERE stu.stunum = ? limit ?,?";
            return this.query(StuAndPro.class,sql,stunum,(currentPage-1)*currentSize,currentSize);
        }else if((stunum==null||"".equals(stunum))&&(name!=null&&!"".equals(name))&&(specialty==null||"".equals(specialty))){
            sql = "SELECT stu.stunum,stu.name,stu.specialty,pro.course,pro.score FROM school_student stu INNER JOIN school_project pro on stu.stunum = pro.stunum WHERE stu.name like '"+names+"' limit ?,?";
            return this.query(StuAndPro.class,sql,(currentPage-1)*currentSize,currentSize);
        }else if((stunum!=null&&!"".equals(stunum))&&(name!=null&&!"".equals(name))&&(specialty==null||"".equals(specialty))){
            sql = "SELECT stu.stunum,stu.name,stu.specialty,pro.course,pro.score FROM school_student stu INNER JOIN school_project pro on stu.stunum = pro.stunum WHERE stu.stunum = ? and stu.name like '"+names+"' limit ?,?";
            return this.query(StuAndPro.class,sql,stunum,(currentPage-1)*currentSize,currentSize);
        }
        sql = "SELECT stu.stunum,stu.name,stu.specialty,pro.course,pro.score FROM school_student stu INNER JOIN school_project pro on stu.stunum = pro.stunum WHERE stu.specialty = ? limit ?,?";
        return this.query(StuAndPro.class,sql,specialty,(currentPage-1)*currentSize,currentSize);
    }


    //    计算总的记录数
    @Override
    public int totalCount(String stunum,String name,String specialty) {
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        int Count = 0;
        String sql = null;
        String names = "%"+name+"%";
        try {
            con = this.getConnection();
            if((stunum!=null&&!"".equals(stunum))&&(name!=null&&!"".equals(name))&&(specialty!=null&&!"".equals(specialty))){
                sql = "SELECT Count(*) FROM school_student stu INNER JOIN school_project pro on stu.stunum = pro.stunum WHERE stu.stunum = ? and stu.name like '"+names+"' and stu.specialty = ?";
            }else if((stunum!=null&&!"".equals(stunum))&&(name==null||"".equals(name))&&(specialty!=null&&!"".equals(specialty))){
                sql = "SELECT Count(*) FROM school_student stu INNER JOIN school_project pro on stu.stunum = pro.stunum WHERE stu.stunum = ? and stu.specialty = ?";
            }else if((stunum==null||"".equals(stunum))&&(name!=null&&!"".equals(name))&&(specialty!=null&&!"".equals(specialty))){
                sql = "SELECT Count(*) FROM school_student stu INNER JOIN school_project pro on stu.stunum = pro.stunum WHERE stu.name like '"+names+"' and stu.specialty = ?";
            }else if((stunum==null||"".equals(stunum))&&(name==null||"".equals(name))&&(specialty==null||"".equals(specialty))){
                sql = "SELECT Count(*) FROM school_student stu INNER JOIN school_project pro on stu.stunum = pro.stunum";
            }else if((stunum!=null&&!"".equals(stunum))&&(name==null||"".equals(name))&&(specialty==null||"".equals(specialty))){
                sql = "SELECT Count(*) FROM school_student stu INNER JOIN school_project pro on stu.stunum = pro.stunum WHERE stu.stunum = ?";
            }else if((stunum==null||"".equals(stunum))&&(name!=null&&!"".equals(name))&&(specialty==null||"".equals(specialty))){
                sql = "SELECT Count(*) FROM school_student stu INNER JOIN school_project pro on stu.stunum = pro.stunum WHERE stu.name like '"+names+"'";
            }else if((stunum!=null&&!"".equals(stunum))&&(name!=null&&!"".equals(name))&&(specialty==null||"".equals(specialty))){
                sql = "SELECT Count(*) FROM school_student stu INNER JOIN school_project pro on stu.stunum = pro.stunum WHERE stu.stunum = ? and stu.name like '"+names+"'";
            }
            else {
                sql = "SELECT Count(*) FROM school_student stu INNER JOIN school_project pro on stu.stunum = pro.stunum WHERE stu.specialty = ?";
            }
            ps = con.prepareStatement(sql);
            if((stunum!=null&&!"".equals(stunum))&&(name!=null&&!"".equals(name))&&(specialty!=null&&!"".equals(specialty))){
                ps.setObject(1,stunum);
                ps.setObject(2,specialty);
            }else if((stunum!=null&&!"".equals(stunum))&&(name==null||"".equals(name))&&(specialty!=null&&!"".equals(specialty))){
                ps.setObject(1,stunum);
                ps.setObject(2,specialty);
            }else if((stunum==null||"".equals(stunum))&&(name!=null&&!"".equals(name))&&(specialty!=null&&!"".equals(specialty))){
                ps.setObject(1,specialty);
            }else if((stunum==null||"".equals(stunum))&&(name==null||"".equals(name))&&(specialty==null||"".equals(specialty))){

            }else if((stunum!=null&&!"".equals(stunum))&&(name==null||"".equals(name))&&(specialty==null||"".equals(specialty))){
                ps.setObject(1,stunum);
            }else if((stunum==null||"".equals(stunum))&&(name!=null&&!"".equals(name))&&(specialty==null||"".equals(specialty))){

            }else if((stunum!=null&&!"".equals(stunum))&&(name!=null&&!"".equals(name))&&(specialty==null||"".equals(specialty))){
                ps.setObject(1,stunum);
            }else {
                ps.setObject(1,specialty);
            }

            rs = ps.executeQuery();
            if(rs.next()){
                Count = rs.getInt(1);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            this.closeAll(rs,ps,null,con);
        }
        return Count;
    }

    //计算总页数
    @Override
    public int totalPage(int pageSize,String stunum,String name,String specialty){
        int totalCount = this.totalCount(stunum,name,specialty);
        int CountPage = 0;
        if(totalCount%pageSize==0){
            CountPage = totalCount/pageSize;
        }else {
            CountPage = (totalCount/pageSize)+1;
        }
        return CountPage;
    }


}
